﻿/*******************************************************************  
 * Copyright (C) vancl.com
 * All rights reserved. 
 * 
 * Created By: VANCLOA\hanxianlong
 * Create Date:2011/8/17 11:48:23
 * Last Modified By:VANCLOA\hanxianlong
 * Last Modified Date:2011/8/17 11:48:23
 * Description: 请添加对类文件的描述
 * ********************************************************************/
#region Using directives
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using SimpleCodeGenerator.Schema;
using SimpleCodeGenerator.Utility;
#endregion

namespace SimpleCodeGenerator.Generators
{
	/// <summary>
	/// 类的说明
	/// </summary>
	class DataLayerGenerator:GeneratorBase
	{
        /// <summary>
        /// 主库连接字符串
        /// </summary>
        public string MainDbConnString { get; set; }

        /// <summary>
        /// 只读库连接字符串
        /// </summary>
        public string ReadonlyDBConnString { get; set; }

		public override void Generate(TableSchema table)
		{
			var sResult = new MyStringBuilder();

			#region common:copyright/ using block
			BuildCopyRight(sResult);
			BuildCommonUsingBlock(sResult);
			BuildMoreUsingBlock(sResult, "System.Data.SqlClient");//add System.Data.SqlClient reference
			BuildMoreUsingBlock(sResult, "System.Data");//add System.Data reference
            BuildMoreUsingBlock(sResult, "Vancl.CMS.Model");//add Model reference

            if (!string.IsNullOrEmpty(Namespace))
            {
                BuildMoreUsingBlock(sResult, "Vancl.CMS.Model" + Namespace);//add Model reference
            }
			sResult.AppendLine(); 
			#endregion

			string datalayerName = table.Code + "Data";

			//namespace start
			#region namespace
			sResult.AppendFormat("namespace Vancl.CMS.Data{0}", Namespace)
				.AppendLine();
			sResult.AppendLine("{"); 
			#endregion

			sResult.IncreaseIndent();//increase indent for class signature
			//class signature
			#region class signature
			//summary
			BuildRemark(sResult, table.Comment + "的数据访问层");
			sResult.AppendFormat( "public static class {0}", datalayerName).AppendLine();
			sResult.AppendLine("{");//class start 
			#endregion

			sResult.IncreaseIndent();
			//genereate insert method
			GenerateInsertMethod(sResult, table);
			sResult.AppendLine();
			sResult.DecreaseIndent();

			sResult.IncreaseIndent();
			//generate update method
			GenerateUpdateMethod(sResult, table);
			sResult.AppendLine();
			sResult.DecreaseIndent();

			sResult.IncreaseIndent();
			GenerateGetItemMethod(sResult, table);
			sResult.AppendLine();
			sResult.DecreaseIndent();

			//genereate delete method
			sResult.IncreaseIndent();
			GenerateDeleteMethod(sResult, table);
			sResult.AppendLine();
			sResult.DecreaseIndent();

			sResult.IncreaseIndent();
			GenerateSearchMethod(sResult, table);
			sResult.AppendLine();
			sResult.DecreaseIndent();

			sResult.IncreaseIndent();
			GenerateSearchOptionsClass(sResult, table);
			sResult.DecreaseIndent();

			sResult.AppendLine("}");//class end

			sResult.DecreaseIndent();//decrease indent for class
			sResult.AppendLine("}");//namespace end

			Save(datalayerName, sResult.ToString());
		}

		private void GenerateGetItemMethod(MyStringBuilder output, TableSchema table)
		{
			#region method signature
			BuildRemark(output, "按主键获取一条记录");
			foreach (var key in table.PrimaryKeyColumns)
			{
				BuildParamRemark(output, key.Code.FirstLetterToLower(), key.Comment);
			}
			BuildReturnRemark(output, "存在该条记录则返回" + table.Code +"Info 类的实例，否则返回null");

			//method signature
			output.AppendFormat("public static {0} GetItem(", table.Code + "Info");

			//method parameters
			foreach (var key in table.PrimaryKeyColumns)
			{
				output.AppendNoLineFormat("{0} {1},", key.DataType, key.Code.FirstLetterToLower());
			}
			output.RemoveFromLast(1);
			output.Append(")")
				.AppendLine();
			#endregion

			output.AppendLine("{");//method start

			output.IncreaseIndent();//method start
			#region sql string
			output.AppendLine("//构造查询SQL语句");

			output.AppendFormat("string sql =\"select * from {0} where ", table.Code);
			table.Columns
				.Where(c => c.IsPrimaryKey == true)
				.ToList().ForEach(c => output.AppendNoLineFormat(" {0}=@{0} and", c.Code));

			//remove the last " and"
			output.RemoveFromLast(4);
			output.Append(";\";");
			output.AppendLine();
			#endregion

			#region SqlParameter array
			//build SqlParameter array
			output.AppendLine("SqlParameter[] parameters =");
			output.AppendLine( "{");//sql parameter array start

			output.IncreaseIndent();
			table.PrimaryKeyColumns
				.ForEach(c =>
				{
					output.AppendFormat("new SqlParameter(\"@{0}\",{1}),", c.Code, c.Code.FirstLetterToLower())
						.AppendLine();
				});
			output.RemoveFromLast(3);
			output.DecreaseIndent();

			output.AppendLine();
			output.AppendLine("};");//sql parameter array end

			output.AppendLine();
			#endregion

			//return 
			output.AppendLine(string.Format("DataRow row = SqlHelper.ExecuteDataRow({0}sql,parameters);",ReadonlyDBConnString));
			output.AppendLine( "if (row != null)");
			output.AppendLine( "{");

			output.IncreaseIndent();

			#region if block
			output.AppendFormat("return new {0}()", table.Code + "Info")
				.AppendLine();
			output.AppendLine("{");//object initialized start

			output.IncreaseIndent();

			#region for each block
			table.Columns.ForEach(c =>
			{
				if (c.IsNumberDataType || c.IsDateTimeType)
				{
					output.AppendFormat("{0} = {1}.Parse(row[\"{0}\"].ToString()),", c.Code, c.DataType)
						.AppendLine();
				}
				else
				{
					output.AppendFormat("{0} = row[\"{0}\"].ToString(),", c.Code)
						.AppendLine();
				}
			}); 
			#endregion

			output.DecreaseIndent();
			output.RemoveFromLast(3);

			output.AppendLine();
			output.AppendLine("};");//object initialized end
			#endregion

			output.DecreaseIndent();

			output.AppendLine("}");
			output.AppendLine("return null;");


			output.DecreaseIndent();//method end
			output.AppendLine("}");//method end
		}

		

		void GenerateInsertMethod(MyStringBuilder output,TableSchema table)
		{
			string paramName = table.Code.FirstLetterToLower();
			#region class signature
			BuildRemark(output, "插入数据库");
			BuildParamRemark(output, paramName, table.Comment + "实例");

			//method signature
			output.AppendFormat("public static int Insert({0}Info {1})", table.Code, paramName)
				.AppendLine();
			#endregion

			output.AppendLine("{");//method start

			output.IncreaseIndent();//increaset indent for method body

			#region sql string
			output.AppendLine("//构造插入SQL语句");

			output.AppendFormat("string sql =\"insert into ");
			output.Append(table.Code + "(");
			table.Columns
				.Where(c => c.IsIdentity == false)
				.ToList().ForEach(c => output.Append(c.Code + ","));

			//remove the last ","
			output.RemoveFromLast(1);
			output.Append(") values(");

			//parameters
			table.Columns
				.Where(c => c.IsIdentity == false)
				.ToList().ForEach(c => output.AppendNoLineFormat("@{0},", c.Code));

			//remove the last ","
			output.RemoveFromLast(1);
			output.Append(");\";");
			#endregion

			output.AppendLine();

			#region sql parameters
			//build SqlParameter array
			output.AppendLine( "SqlParameter[] parameters =");
			output.AppendLine("{");//sql parameter array start

			output.IncreaseIndent();//increate indent for object intialized
			table.Columns
				.Where(c => c.IsIdentity == false)
				.ToList()
				.ForEach(c =>
				{
					output.AppendFormat("new SqlParameter(\"@{0}\",{1}.{0}),", c.Code, paramName)
						.AppendLine();
				});
			output.RemoveFromLast(1);
			output.DecreaseIndent();//increate indent for object intialized

			output.AppendLine("};");//sql parameter array end
			output.AppendLine(); 
			#endregion

			//return 
            output.AppendLine(string.Format("return SqlHelper.ExecuteNonQuery({0}sql, parameters);", MainDbConnString));

			output.DecreaseIndent();//decrease indent for method body

			output.AppendLine("}");//method end
		}

		void GenerateDeleteMethod(MyStringBuilder output, TableSchema table)
		{
			#region method signature
			BuildRemark(output, "按主键删除记录");
			foreach (var key in table.PrimaryKeyColumns)
			{
				BuildParamRemark(output, key.Code.FirstLetterToLower(), key.Comment);
			}
			BuildReturnRemark(output, "受影响的条数");

			//method signature
			output.AppendFormat("public static int Delete(");

			//method parameters
			foreach (var key in table.PrimaryKeyColumns)
			{
				output.AppendNoLineFormat("{0} {1},", key.DataType, key.Code.FirstLetterToLower());
			}
			output.RemoveFromLast(1);
			output.Append(")");
			output.AppendLine();
			#endregion

			output.AppendLine("{");//method start
			output.IncreaseIndent();//increate indent for method body
			#region sql string
			output.AppendLine("//构造删除SQL语句");

			output.AppendFormat("string sql =\"delete from {0} where ", table.Code);
			table.Columns
				.Where(c => c.IsPrimaryKey == true)
				.ToList().ForEach(c => output.AppendNoLineFormat(" {0}=@{0} and", c.Code));

			//remove the last "and"
			output.RemoveFromLast(3);
			output.Append(";\";");
			output.AppendLine();
			#endregion

			#region SqlParameter array
			//build SqlParameter array
			output.AppendLine("SqlParameter[] parameters =");
			output.AppendLine("{");//sql parameter array start

			output.IncreaseIndent();//increate indent for object initialization
			table.Columns
				.Where(c => c.IsPrimaryKey == true)
				.ToList()
				.ForEach(c =>
				{
					output.AppendFormat("new SqlParameter(\"@{0}\",{1}),", c.Code, c.Code.FirstLetterToLower())
						.AppendLine();
				});
			output.RemoveFromLast(1);

			output.DecreaseIndent();//decreate indent for object initialization
			output.AppendLine("};");//sql parameter array end

			output.AppendLine(); 
			#endregion

            //return 
            output.AppendLine(string.Format("return SqlHelper.ExecuteNonQuery({0}sql, parameters);", MainDbConnString));

			output.DecreaseIndent();//decrease indent for method body
			output.AppendLine("}");//method end
		}

		void GenerateUpdateMethod(MyStringBuilder output, TableSchema table)
		{
			#region method signature
			BuildRemark(output, "更新记录");
			BuildParamRemark(output, table.Code.FirstLetterToLower(), "实体对象");
			BuildReturnRemark(output, "受影响的条数");

			//method signature
			output.AppendFormat("public static int Update({0} {1})",table.Code + "Info", table.Code.FirstLetterToLower())
				.AppendLine();
			#endregion

			output.AppendLine("{");//method start

			output.IncreaseIndent(); //increase indent for method body
			#region sql string
			output.AppendLine("//构造更新SQL语句");

			output.AppendFormat("string sql =\"update {0} set ", table.Code);
			table.Columns
				.Where(c => c.IsPrimaryKey == false)
				.ToList()
				.ForEach(c => output.AppendNoLineFormat("{0}=@{0},", c.Code));

			//remove the last ","
			output.RemoveFromLast(1);

			output.Append(" where ");
			//conditions
			table.Columns.Where(c => c.IsPrimaryKey)
				.ToList().ForEach(c => output.AppendNoLineFormat("{0}=@{0} and", c.Code));

			output.RemoveFromLast(4);
			output.Append(";\";");
			output.AppendLine();
			#endregion

			#region Sql parameter
			//build SqlParameter array
			output.AppendLine("SqlParameter[] parameters =");
			output.AppendLine("{");//sql parameter array start

			output.IncreaseIndent();//increase indent for object initialization
			table.Columns
				.ForEach(c =>
				{
					output.AppendFormat("new SqlParameter(\"@{0}\",{1}.{0}),", c.Code, table.Code.FirstLetterToLower())
						.AppendLine();
				});
			output.RemoveFromLast(3);

			output.AppendLine();
			output.DecreaseIndent();//decrease indent for object initialization

			output.AppendLine("};");//sql parameter array end

			output.AppendLine(); 
			#endregion

            
            //return 
            output.AppendLine(string.Format("return SqlHelper.ExecuteNonQuery({0}sql, parameters);", MainDbConnString));
             
			output.DecreaseIndent(); //decrease indent for method body

			output.AppendLine("}");//method end
		}

		void GenerateSearchMethod(MyStringBuilder output, TableSchema table)
		{
			#region method signature
			BuildRemark(output, "搜索数据表中的记录");
			BuildParamRemark(output, "pageIndex", "当前显示第几页(从1开始)");
			BuildParamRemark(output, "pageSize", "每页显示条数");
			BuildParamRemark(output, "options", "搜索条件，请参照SearchOptions类的各个字段说明具体使用");
			BuildParamRemark(output, "isAscending", "是否按照主键升序排序，默认为升序排列");
			BuildReturnRemark(output, "记录列表");
			//method signature
			output.AppendFormat("public static IPageOfList<{0}> Search(int pageIndex, int pageSize,SearchOptions options,bool isAscending = true)", table.Code + "Info");
			#endregion
			output.AppendLine();
			output.AppendLine("{");

			output.IncreaseIndent();//increase indent for method body

			//append Sql parameter list
			output.AppendLine("List<SqlParameter> parameters = new List<SqlParameter>();");
			output.AppendLine("string conditions = \" 1=1 \";");

			#region build conditions
			output.AppendLine( "#region build conditions");
			foreach (var column in table.Columns)
			{
				if (column.IsNumberDataType || column.IsDateTimeType)
				{
					output.AppendFormat("if(options.{0} == null)", column.Code).AppendLine();
					output.AppendLine("{");

					output.IncreaseIndent();//increase indent for first level if block
					//范围内查询，判断start是否为空
					output.AppendFormat("if(options.{0}Start != null)", column.Code)
						.AppendLine();
					output.AppendLine("{");

					output.IncreaseIndent();//increase indent for second if block
					output.AppendFormat("conditions +=\" and {0}>=@{0}Start\";", column.Code)
						.AppendLine();
					output.AppendFormat("parameters.Add(new SqlParameter(\"@{0}Start\", options.{0}Start));", column.Code)
						.AppendLine();

					output.DecreaseIndent();//decrease indent for second if block
					output.AppendLine("}");

					//判断end是否为空
					output.AppendFormat("if(options.{0}End != null)", column.Code)
						.AppendLine();
					output.AppendLine("{");

					output.IncreaseIndent();//increase indent for third if block
					output.AppendFormat("conditions +=\" and {0}<=@{0}End\";", column.Code)
						.AppendLine();
					output.AppendFormat("parameters.Add(new SqlParameter(\"@{0}End\", options.{0}End));", column.Code)
						.AppendLine();

					output.DecreaseIndent();//decrease indent for third if block
					output.AppendLine("}");

					output.DecreaseIndent();//decrease indent for  first level if block
					//第一级if结束
					output.AppendLine("}");

					//当可空值类型不为null时，按该条件精确匹配
					output.AppendLine("else");
					output.AppendLine("{");

					output.IncreaseIndent();//increase indent for else block
					output.AppendFormat("conditions +=\" and {0}=@{0}\";", column.Code)
						.AppendLine();
					output.AppendFormat("parameters.Add(new SqlParameter(\"@{0}\", options.{0}));", column.Code)
						.AppendLine();

					output.DecreaseIndent();//decrease indent for else block
					output.AppendLine("}");
				}
				else
				{
					output.AppendFormat("if(!string.IsNullOrEmpty(options.{0}))", column.Code)
						.AppendLine();
					output.AppendLine("{");

					output.IncreaseIndent();//increase indent for if block

					output.AppendFormat("conditions +=\" and {0}=@{0}\";", column.Code)
						.AppendLine();
					output.AppendFormat("parameters.Add(new SqlParameter(\"@{0}\", options.{0}));", column.Code)
						.AppendLine();

					output.DecreaseIndent();//decrease indent for if block
					output.AppendLine("}");
				}

				//generate a new line between two if blocks
				output.AppendLine();
			}
			output.AppendLine("#endregion build conditions"); 
			#endregion

			output.AppendLine();

			#region fast paging
			output.AppendLine("FastPaging fp = new FastPaging();");
			output.AppendFormat("fp.TableName = \"{0}\";",table.Code)
				.AppendLine();
			output.AppendLine("fp.TableReName = \"t1\";");
			output.AppendLine("fp.PageIndex = pageIndex;");
			output.AppendLine("fp.Ascending = isAscending;");
			output.AppendLine("fp.PageSize = pageSize;");
			output.AppendFormat("fp.PrimaryKey = \"");
			foreach (var pkcolumn in table.PrimaryKeyColumns)
			{
				output.Append(pkcolumn.Code + ",");
			}
			output.RemoveFromLast(1);
			output.Append("\";");
			output.AppendLine();

			output.AppendFormat("fp.QueryFields = \"");
			foreach (var column in table.Columns)
			{
				output.Append("t1." + column.Code + ",");
			}
			output.RemoveFromLast(1);
			output.Append("\";");
			output.AppendLine();

			output.AppendLine("fp.WithOptions = \" WITH(NOLOCK)\";");
			output.AppendLine("fp.Condition =conditions;");
			#endregion

			output.AppendLine();
			output.AppendLine( "string sql = fp.Build();");
			output.AppendFormat("List<{0}> list = new List<{0}>();", table.Code + "Info")
				.AppendLine();

			#region using region read data from Sql DataReader

			output.AppendLine(string.Format("using (SqlDataReader reader = SqlHelper.ExecuteReader({0}sql, parameters.ToArray()))",ReadonlyDBConnString));
			output.AppendLine("{");

			output.IncreaseIndent();//increase indent for using block

			output.AppendLine("while (reader.Read())");
			output.AppendLine("{");//while start

			output.IncreaseIndent();//increase indent for while loop
			output.AppendFormat( "list.Add(new {0}Info()",table.Code).AppendLine();
			output.AppendLine("{");//new model start

			output.IncreaseIndent();//increase indent for object initialization
			foreach (var column in table.Columns)
			{
				string format = column.Code + " = {0}reader[\"" + column.Code +"\"].ToString(){1}";
				string columnString = "";
				if (column.IsDateTimeType || column.IsNumberDataType)
				{
					columnString += string.Format(format, column.DataType + ".Parse(","),");
				}
				else
				{
					columnString += string.Format(format, "", ",");
				}
				output.AppendLine(columnString);
			}

			//remove the last ","
			output.RemoveFromLast(3);
			output.AppendLine();

			output.DecreaseIndent();//decrease indent for object initialization
			output.AppendLine("});");//object intialization end

			output.DecreaseIndent();//decrease indent for while loop
			output.AppendLine("}");//while end

			output.DecreaseIndent();//decrease indent for using block

			output.AppendLine("}");//using block end
			#endregion using block end

			output.AppendLine();
			output.AppendLine(string.Format("int totalItemCount = Convert.ToInt32(SqlHelper.ExecuteScalar({0}\"select count(*) from " + table.Code +" where \" + conditions,parameters.ToArray()));",ReadonlyDBConnString));
			output.AppendLine("return new PageOfList<" + table.Code + "Info>(list, pageIndex, pageSize, totalItemCount);");
			
			output.DecreaseIndent();//increase indent for method body
			output.AppendLine("}");
		}

		/// <summary>
		/// 生成查询选项类
		/// </summary> 
		private void GenerateSearchOptionsClass(MyStringBuilder output, TableSchema table)
		{
			BuildRemark(output, table.Code + "的查询辅助类");
			output.AppendLine("public class SearchOptions");
			output.AppendLine("{");

			output.IncreaseIndent();

			foreach (var column in table.Columns)
			{
				string comment = column.Comment + ",如果该条件赋值";
				if (column.IsNumberDataType || column.IsDateTimeType)
				{
					comment += ",则" + column.Code + "Start 和" + column.Code + "End 两个字段的条件自动失效";
				}
				comment += ",按该字段精确匹配";

				BuildRemark(output, comment);

				string dataType = (column.IsNumberDataType || column.IsDateTimeType) ? column.DataType + "?" : column.DataType;

				output.AppendFormat("public {0} {1} {{ get; set; }}", dataType, column.Code)
					.AppendLine();

				if (column.IsDateTimeType)
				{
					BuildRemark(output, column.Comment + "，查询开始时间");
					output.AppendFormat("public {0}? {1}Start {{ get; set; }}", column.DataType, column.Code)
						.AppendLine();

					BuildRemark(output, column.Comment + "，查询结束时间");
					output.AppendFormat("public {0}? {1}End {{ get; set; }}", column.DataType, column.Code)
						.AppendLine();
				}

				if (column.IsNumberDataType)
				{
					BuildRemark(output, column.Comment + "，查询最小值");
					output.AppendFormat("public {0}? {1}Start {{ get; set; }}", column.DataType, column.Code)
						.AppendLine();

					BuildRemark(output, column.Comment + "，查询最大值");
					output.AppendFormat("public {0}? {1}End {{ get; set; }}", column.DataType, column.Code)
						.AppendLine();
				}

				output.AppendLine();
			}

			output.DecreaseIndent();
			output.AppendLine("}");
		}
	}
}
